<%@ page import="java.sql.Statement" import="java.sql.ResultSet" import="java.sql.Connection" %>
<%@page import="java.sql.CallableStatement"%> 
<%@page import="connection.DBConnection"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 

"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Balance Sheet</title>
</head>
<body>
<form name="balancesheet">

  <%
        String exportToExcel = request.getParameter("exportToExcel");
        if (exportToExcel != null && exportToExcel.toString().equalsIgnoreCase("YES")) 
        {
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "inline; filename=" + "BalanceSheetReport.xls");
 
        }
    %>
    
<center><h2> Balance Sheet </h2> </center>

<%
 
		DBConnection dbc=new DBConnection();
		Connection con=dbc.getNewConnection();
		
		Statement st = null;
		ResultSet rs = null;   
		CallableStatement cs = null;
		
		double openingstock=0.0;
		double administrativecharges=0.0;
		double purchase=0.0, purchasetransport=0.0, purchaseothercharges=0.0;		
		double debit=0.0, debittransport=0.0, debitothercharges=0.0;
		double sales=0.0,salestransport=0.0;
		double credit=0.0,credittransport=0.0;
		double closingstock=0.0;
		double bankcharges=0.0;
		double indirectincome=0.0;		
		double salarywages=0.0;		
		double sellingandmarketing=0.0;		
		double depreciation=0.0;		
		double otherexpenses=0.0;		
				
		double capital=0.0;
		double fixedassets=0.0;
		double securedloans=0.0;
		double investments=0.0;
		double unsecuredloans=0.0;
		double sundrydebtors=0.0,salestax=0.0;
		double credittax=0.0;
		double loansandadvances=0.0;
		double sundrycreditor=0.0,purchasetax=0.0;
		double debittax=0.0;
		double receiptsbank=0.0;
		double paymentbank=0.0;
		double provision=0.0;
		
		try
   		{
		   cs=con.prepareCall("{call PNL2}");
		   rs=cs.executeQuery();
		   
		 	while(rs.next())
		 	{
			    String a1=rs.getString(1); // group name
			    double a2=rs.getDouble(2); // amount
			    
			    out.print("<br>"+ a1 +":"+ a2);
			    
			    if(a1.equals("Opening Stock"))
			   	{
				   openingstock=a2;
			   	}
			    else if(a1.equals("closing Stock"))
			   	{
				   closingstock=a2;
			   	}
			    else if(a1.equals("Administrative Charges"))
			   	{
				   administrativecharges=a2;
			   	}
			  	else if(a1.equals("Bank Charges and Interst"))
			  	{
			  		bankcharges=a2;
			  	}
			  	else if(a1.equals("Indirect Income"))
			  	{
			  		indirectincome=a2;
			  	}
			  	else if(a1.equals("Selling and Marketing Expenses"))
			  	{
			  		sellingandmarketing=a2;
			  	}
			  	else if(a1.equals("Salary and Wages"))
			  	{
			  		salarywages=a2;
			  	}
			  	else if(a1.equals("Depreciation"))
			  	{
			  		depreciation=a2;
			  	}
			  	else if(a1.equals("Other Expenses"))
			  	{
			  		otherexpenses=a2;
			  	}
			  	else if(a1.equals("Bank Charges and Interest"))
			  	{
			  		bankcharges=a2;
			  	}
			  	else if(a1.equals("Sales Net"))
			  	{
			  		sales=a2;
			  	}
			  	else if(a1.equals("Sales Transport"))
			  	{
			  		salestransport=a2;
			  	}
			  	else if(a1.equals("Sales Tax"))
			  	{
			  		salestax=a2;
			  	}
			  	else if(a1.equals("Credit Net"))
			  	{
			  		credit=a2;
			  	}
			  	else if(a1.equals("Credit Transport"))
			  	{
			  		credittransport=a2;
			  	}
			  	else if(a1.equals("Credit Tax"))
			  	{
			  		credittax=a2;
			  	}
			  	else if(a1.equals("Purchase Net"))
			  	{
			  		purchase=a2;
			  	}
			  	else if(a1.equals("Purchase Transport"))
			  	{
			  		purchasetransport=a2;
			  	}
			  	else if(a1.equals("Purchase Other"))
			  	{
			  		purchaseothercharges=a2;
			  	}
			  	else if(a1.equals("Purchase Tax"))
			  	{
			  		purchasetax=a2;
			  	}
			  	else if(a1.equals("Debit Net"))
			  	{
			  		debit=a2;
			  	}
			  	else if(a1.equals("Debit Transport"))
			  	{
			  		debittransport=a2;
			  	}
			  	else if(a1.equals("Debit Other"))
			  	{
			  		debitothercharges=a2;
			  	}
			  	else if(a1.equals("Debit Tax"))
			  	{
			  		debittax=a2;
			  	}
			  	else if(a1.equals("Capital"))
			  	{
			  		capital=a2;
			  	}
			  	else if(a1.equals("Fixed Assets"))
			  	{
			  		fixedassets=a2;
			  	}
			  	else if(a1.equals("Secured Loans"))
			  	{
			  		securedloans=a2;
			  	}
			  	else if(a1.equals("Investments"))
			  	{
			  		investments=a2;
			  	}
			  	else if(a1.equals("Unsecured Loans"))
			  	{
			  		unsecuredloans=a2;
			  	}
			  	else if(a1.equals("Sundry Debtors"))
			  	{
			  		sundrydebtors=a2;
			  	}
			  	else if(a1.equals("Sundry Creditors"))
			  	{
			  		sundrycreditor=a2;
			  	}
			  	else if(a1.equals("Payment Bank"))
			  	{
			  		paymentbank=paymentbank+a2;
			  	}
			  	else if(a1.equals("Receipts Bank"))
			  	{
			  		receiptsbank=receiptsbank+a2;
			  	}
			  	else if(a1.equals("Provision"))
			  	{
			  		provision=a2;
			  	}
			  	

		 	}
		  
   		}
		catch(Exception e) {}	
		
		double Total2=salestransport+closingstock+sales-credit-credittransport;

		double Subtotal1=openingstock+purchase+purchasetransport+purchaseothercharges-debit-debittransport-debitothercharges;
	
		double grossprofit=Total2-Subtotal1;
					
		double netprofit= grossprofit+indirectincome-administrativecharges-bankcharges-sellingandmarketing-salarywages-depreciation-otherexpenses;
		
		out.println("total2"+Total2);
		out.println("gross profit"+grossprofit);
		out.print("net profit"+netprofit);
%>

<% 

		
%>

<table align="center" border=2 width=700>
<tr>
<th colspan=6><font color=red>Balance Sheet</font></th>
</tr>

<tr>
<th ><font color=red>Liabilities</font></th>
<th><font color=red>Rs.</font></th>
<th><font color=red>Rs.</font></th>
<th><font color=red>Assets</font></th>
<th><font color=red>Rs.</font></th>
<th><font color=red>Rs.</font></th>
</tr>

<% capital=-(capital); %>
<tr>
	<td>Capital</td>
	<td></td>
	<td><%=capital%></td>
	
	<td>Fixed Assets</td>
	<td></td>
	<td><%=fixedassets %></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>


<tr>
	<td>Secured loans</td>
	<td></td>
	<td><%=securedloans %></td>
	
	<% investments=-(investments); %>
	
	<td>Investments</td>
	<td></td>
	<td><%=investments %></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>


<tr>
	<td>Unsecured loans</td>
	<td></td>
	<td><%=unsecuredloans %></td>
	
	
	<th><font color=red>Current Assets:</font></th>
	<td></td>
	<td></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

<tr>
	<td></td>
	<td></td>
	<td></td>
	<td>Closing Stock</td>
	<td><%=closingstock %></td>
	<td></td>
</tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>


<%-- <% sundrydebtors= -(sundrydebtors); %> --%>

<tr>
	<td></td>
	<td></td>
	<td></td>
	<td>Sundry Debtors</td>
	<td><%=sundrydebtors %></td>
	<td></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>


<tr>
	<th><font color=red>Current Liabilities:</font></th>
	<td></td>
	<td></td>
	<td>Loans and Advances</td>
	<td><%=loansandadvances %></td>
	<td></td>
</tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>



<% sundrycreditor= -(sundrycreditor); %>

<tr>
	<td>Sundry Creditors</td>
	<td><%=sundrycreditor %></td>
	<td></td>
	
	<% double cashbank=receiptsbank-paymentbank; %>
	
	<td>Cash and Bank Balance</td>
	<td><%=cashbank %></td>
	
	<% double subtotal1=loansandadvances+sundrydebtors+closingstock+cashbank; %>
	
	<td><%=subtotal1 %></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

<% 		double dutiesandtaxes=0.0;
		dutiesandtaxes=-purchasetax+salestax+debittax-credittax; %>
		
<tr>
	<td>Duties and Taxes</td>
	<td><%=dutiesandtaxes %></td>
	<td></td>
	<td></td>
	<td></td>
	<td></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

<tr>
	<td>Provision</td>
	<td><%=provision %></td>	
	
	<% double subtotal2=sundrycreditor+dutiesandtaxes+provision; %>
	
	<td><%=subtotal2 %></td>
	<td></td>
	<td></td>
	<td></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

		
<tr>
	<td>Profit and Loss Account</td>
	<td><%=netprofit %></td>
	<td></td>
	<td></td>
	<td></td>
	<td></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

		<% double total1=subtotal2+capital+securedloans+unsecuredloans+netprofit; %>
 
<tr>
	<th><font color=red>Total</font></th>
	<td></td>
	<td><%=total1 %></td>

		<% double total2=subtotal1+investments+fixedassets; %>
		
	<th><font color=red>Total</font></th>
	<td></td>
	<td><%=total2 %></td>
</tr>


</table>

<br><br> 
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

    <%
        if (exportToExcel == null) {
    %>
    <a href="Balancesheet2.jsp?exportToExcel=YES">Export to Excel</a>
    <%
        }
    %>

<center><input type="button" value="Print this page" onClick="window.print()"></center>

</form>
</body>
</html>